from sqlalchemy.orm import Session, joinedload
from sqlalchemy import and_, or_, desc, func
from app.models.message import Message, MessageType, MessageStatus
from app.models.user import User, UserRole
from app.schemas.message import UserMessageCreate, AdminMessageCreate, MessageReply
from typing import List, Optional
import math

# 用户发送消息给管理员
def create_user_message(db: Session, message: UserMessageCreate, sender_id: int):
    # 获取第一个管理员作为接收者
    admin = db.query(User).filter(User.role == UserRole.ADMIN, User.is_active == True).first()
    if not admin:
        raise ValueError("没有可用的管理员")
    
    # 创建消息对象
    db_message = Message(
        sender_id=sender_id,
        receiver_id=admin.id,
        title=message.title,
        content=message.content,
        message_type=MessageType.USER_TO_ADMIN
    )
    
    # 保存到数据库
    db.add(db_message)
    db.commit()
    db.refresh(db_message)
    return db_message

# 管理员发送消息给用户
# create_admin_message
def create_admin_message(db: Session, message: AdminMessageCreate, sender_id: int):
    # 创建消息对象
    # 先校验 parent_id 合法性（存在且未被软删除）
    parent_message = None
    if message.parent_id is not None:
        parent_message = db.query(Message).filter(
            Message.id == message.parent_id,
            Message.is_deleted == False
        ).first()
        if not parent_message:
            raise ValueError("父消息不存在或已删除")
    
    db_message = Message(
        sender_id=sender_id,
        receiver_id=message.receiver_id,
        title=message.title,
        content=message.content,
        message_type=MessageType.ADMIN_TO_USER,
        parent_id=parent_message.id if parent_message else None
    )
    
    # 如果是回复消息，更新父消息状态
    if parent_message:
        parent_message.status = MessageStatus.REPLIED
    
    # 保存到数据库
    db.add(db_message)
    db.commit()
    db.refresh(db_message)
    return db_message

# 获取会话列表（邮件收件箱效果）
def get_conversations(db: Session, current_user_id: int, page: int = 1, size: int = 20):
    """获取当前用户的所有会话，每个会话返回最新一条消息"""
    offset = (page - 1) * size

    # 筛选当前用户参与的所有消息
    base_filter = and_(
        Message.is_deleted == False,
        or_(
            Message.sender_id == current_user_id,
            Message.receiver_id == current_user_id
        )
    )

    # 生成会话标识：两个用户ID的有序对
    pair_a = func.least(Message.sender_id, Message.receiver_id)
    pair_b = func.greatest(Message.sender_id, Message.receiver_id)

    # 使用窗口函数为每个会话找到最新消息
    subq = db.query(
        Message.id.label("id"),
        pair_a.label("a"),
        pair_b.label("b"),
        Message.created_at.label("created_at"),
        func.row_number().over(
            partition_by=(pair_a, pair_b),
            order_by=(Message.created_at.desc(), Message.id.desc())
        ).label("rn")
    ).filter(base_filter).subquery()

    # 计算总会话数
    total = db.query(func.count()).select_from(subq).filter(subq.c.rn == 1).scalar()

    # 获取分页后的会话ID列表
    subq_paged = db.query(
        subq.c.id,
        subq.c.created_at
    ).filter(
        subq.c.rn == 1
    ).order_by(
        subq.c.created_at.desc()
    ).offset(offset).limit(size).subquery()

    # 根据ID列表获取完整的消息信息
    messages = db.query(Message).options(
        joinedload(Message.sender),
        joinedload(Message.receiver)
    ).join(
        subq_paged, Message.id == subq_paged.c.id
    ).order_by(
        subq_paged.c.created_at.desc()
    ).all()

    return {
        "messages": messages,
        "total": total,
        "page": page,
        "size": size,
        "pages": math.ceil(total / size) if size > 0 else 0
    }

# 新增：管理员收件箱，只显示“用户发来的”，每个用户一条（最新），按时间倒序
def get_admin_inbox(db: Session, admin_id: int, page: int = 1, size: int = 20):
    offset = (page - 1) * size

    subq = db.query(
        Message.id.label("id"),
        Message.sender_id.label("sender_id"),
        Message.created_at.label("created_at"),
        func.row_number().over(
            partition_by=Message.sender_id,  # 每个用户一组
            order_by=(Message.created_at.desc(), Message.id.desc())
        ).label("rn")
    ).join(
        User, Message.sender_id == User.id
    ).filter(
        and_(
            Message.is_deleted == False,
            Message.receiver_id == admin_id,    # 发给当前管理员
            User.role == UserRole.USER          # 发送方是普通用户
        )
    ).subquery()

    total = db.query(func.count()).select_from(subq).filter(subq.c.rn == 1).scalar()

    subq_paged = db.query(
        subq.c.id, subq.c.created_at
    ).filter(
        subq.c.rn == 1
    ).order_by(
        subq.c.created_at.desc()
    ).offset(offset).limit(size).subquery()

    messages = db.query(Message).options(
        joinedload(Message.sender),
        joinedload(Message.receiver)
    ).join(
        subq_paged, Message.id == subq_paged.c.id
    ).order_by(
        subq_paged.c.created_at.desc()
    ).all()

    return {
        "messages": messages,
        "total": total,
        "page": page,
        "size": size,
        "pages": math.ceil(total / size) if size > 0 else 0
    }

# 新增：用户收件箱，只显示“管理员发来的”，每个管理员一条（最新），按时间倒序
def get_user_inbox(db: Session, user_id: int, page: int = 1, size: int = 20):
    offset = (page - 1) * size

    subq = db.query(
        Message.id.label("id"),
        Message.sender_id.label("sender_id"),
        Message.created_at.label("created_at"),
        func.row_number().over(
            partition_by=Message.sender_id,  # 每个管理员一组（如有多个管理员）
            order_by=(Message.created_at.desc(), Message.id.desc())
        ).label("rn")
    ).join(
        User, Message.sender_id == User.id
    ).filter(
        and_(
            Message.is_deleted == False,
            Message.receiver_id == user_id,     # 发给当前用户
            User.role == UserRole.ADMIN         # 发送方是管理员
        )
    ).subquery()

    total = db.query(func.count()).select_from(subq).filter(subq.c.rn == 1).scalar()

    subq_paged = db.query(
        subq.c.id, subq.c.created_at
    ).filter(
        subq.c.rn == 1
    ).order_by(
        subq.c.created_at.desc()
    ).offset(offset).limit(size).subquery()

    messages = db.query(Message).options(
        joinedload(Message.sender),
        joinedload(Message.receiver)
    ).join(
        subq_paged, Message.id == subq_paged.c.id
    ).order_by(
        subq_paged.c.created_at.desc()
    ).all()

    return {
        "messages": messages,
        "total": total,
        "page": page,
        "size": size,
        "pages": math.ceil(total / size) if size > 0 else 0
    }

# 获取与特定用户的对话历史
def get_conversation_with_user(db: Session, current_user_id: int, other_user_id: int, page: int = 1, size: int = 50):
    """获取当前用户与指定用户的完整对话历史"""
    offset = (page - 1) * size
    
    # 查询两用户之间的所有消息
    query = db.query(Message).options(
        joinedload(Message.sender),
        joinedload(Message.receiver)
    ).filter(
        and_(
            Message.is_deleted == False,
            or_(
                and_(Message.sender_id == current_user_id, Message.receiver_id == other_user_id),
                and_(Message.sender_id == other_user_id, Message.receiver_id == current_user_id)
            )
        )
    ).order_by(Message.created_at.asc())  # 按时间正序排列，像邮件对话
    
    # 获取总数
    total = query.count()
    
    # 分页查询
    messages = query.offset(offset).limit(size).all()
    
    return {
        "messages": messages,
        "total": total,
        "page": page,
        "size": size,
        "pages": math.ceil(total / size) if size > 0 else 0
    }

# 获取用户的消息列表（收件箱）
def get_user_messages(db: Session, user_id: int, page: int = 1, size: int = 20):
    # 计算偏移量
    offset = (page - 1) * size
    
    # 查询消息
    query = db.query(Message).options(
        joinedload(Message.sender),
        joinedload(Message.receiver)
    ).filter(
        and_(
            Message.receiver_id == user_id,
            Message.is_deleted == False
        )
    ).order_by(desc(Message.created_at))
    
    # 获取总数
    total = query.count()
    
    # 分页查询
    messages = query.offset(offset).limit(size).all()
    
    return {
        "messages": messages,
        "total": total,
        "page": page,
        "size": size,
        "pages": math.ceil(total / size)
    }

# 获取管理员的消息列表
def get_admin_messages(db: Session, admin_id: int, page: int = 1, size: int = 20):
    # 计算偏移量
    offset = (page - 1) * size
    
    # 查询发给管理员的消息
    query = db.query(Message).options(
        joinedload(Message.sender),
        joinedload(Message.receiver)
    ).filter(
        and_(
            Message.receiver_id == admin_id,
            Message.is_deleted == False
        )
    ).order_by(desc(Message.created_at))
    
    # 获取总数
    total = query.count()
    
    # 分页查询
    messages = query.offset(offset).limit(size).all()
    
    return {
        "messages": messages,
        "total": total,
        "page": page,
        "size": size,
        "pages": math.ceil(total / size)
    }

# 获取消息详情
def get_message_by_id(db: Session, message_id: int, user_id: int):
    # 查询消息并验证权限
    message = db.query(Message).options(
        joinedload(Message.sender),
        joinedload(Message.receiver)
    ).filter(
        and_(
            Message.id == message_id,
            or_(
                Message.sender_id == user_id,
                Message.receiver_id == user_id
            ),
            Message.is_deleted == False
        )
    ).first()
    
    return message

# 标记消息为已读
def mark_conversation_as_read(db: Session, current_user_id: int, other_user_id: int) -> int:
    # 查询消息
    updated = db.query(Message).filter(
        and_(
            Message.is_deleted == False,
            Message.receiver_id == current_user_id,   # 我是接收者
            Message.sender_id == other_user_id,       # 对方是发送者
            Message.status == MessageStatus.UNREAD
        )
    ).update(
        {
            Message.status: MessageStatus.READ,
            Message.read_at: func.now()
        },
        synchronize_session=False
    )
    db.commit()
    return updated

# 获取未读消息数量
def get_unread_count(db: Session, user_id: int):
    # 统计未读消息数量
    count = db.query(Message).filter(
        and_(
            Message.receiver_id == user_id,
            Message.status == MessageStatus.UNREAD,
            Message.is_deleted == False
        )
    ).count()
    
    return count

# 删除消息（软删除）
def delete_message(db: Session, message_id: int, user_id: int):
    # 查询消息并验证权限
    message = db.query(Message).filter(
        and_(
            Message.id == message_id,
            or_(
                Message.sender_id == user_id,
                Message.receiver_id == user_id
            )
        )
    ).first()
    
    if message:
        message.is_deleted = True
        db.commit()
        return True
    
    return False